home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Magnum One
/
Magnum One (Mid-American Digital) (Disc Manufacturing).iso
/
d18
/
lotus1.arc
/
UNIT123.PAS
< prev
Wrap
Pascal/Delphi Source File
|
1991-04-28
|
23KB
|
638 lines
{$N+,E+} (* $N+ compiles for 80x87 which is used *)
(* automatically if available. E+ activates the *)
(* 80X87 emulator which will be used if a coprocessor *)
(* is not present. I understand that these are program *)
(* wide options and cannot be used in a unit separately *)
(* Written by Dan Glanz, Alexandria, Virginia (76672,2572), May, 1989. *)
(* as a public service. *)
(* There are no restrictions on use and no gaurantees that it works. *)
(* All I ask is a smidgeon of credit. *)
(* If you include this in a program, leave the credit line in. *)
(* If you modify the unit, add your own credit line. *)
(* This is a Turbo Pascal 5.0 unit designed to allow reading and writing
of Lotus 1-2-3, Symphony, VP-Planner and other such files using the
Lotus 1-2-3 file format.
************** update information ***************
This version is updated to allow reading and writing of range names and
extents and column widths. The structure of the "Lotus_Record_Type" has
been modified to variant records for efficiency in data storage.
**************************************************
Lotus 1-2-3 uses 8 byte reals (TP's double's). Any program using
Lotus 1-2-3 data must either use a math coprocessor {$N+} or
coprocessor emulation {$N+,E+}
For demonstration purposes, a separate program called TEST123
is included in the ARC file.
It reads any Lotus format file and copies out label, integer, real
column width, range identification and the current value of formula cells
to a file in the same directory with the same name but with an extension
of '.WK!' It does not copy formulas and other such information.
It is primarily designed to allow access to the DATA. However, it
does read column widths, and range names and extents.
Take note that Lotus rows and columns are numbered starting with 0.
that is Column A is 0 and Row 1 is 0.
Lotus_Version is set up as a typed constant as if the file were
a Lotus version 1.0 or 1a type file. Change it if you need to.
The program automatically writes a version record at the beginning of
the file when the file is opened for writing by calling
Open_Lotus_Write_File. It must do this or Lotus 1-2-3 will not
allow use of the file. If you have used Open_Lotus_Read_File to open
a file to be copied or accessed, then the version of that file is
substituted for the default Lotus_Version.
If you want to use the unit to create a Lotus formatted file directly,
you must provide the row and column of the data in Lotus.Row and
Lotus.Column (starting with row 0 and column 0), define the format
in Lotus.Format (default seems to be 255) set the value in either
Lotus.Integer_Value, Lotus.Real_Value, Lotus.Column_Width, or
Lotus.Range_Name, Range_Start Column, etc., or Lotus.Label_Value.
Then set Lotus.Cell_Type := to Integer_Type, Real_Type, Column_Width_Type,
RAnge_Type or Label_Type as the case may be and call Write_Lotus_Record.
Note: When you write your own labels in Label_Value, make sure you put
a ' or " or ^ as the first character of the string. Also, you may be
able to include formulas in a worksheet you are creating by writing out
a label cell containing with the formula and then deleting
the ', ", or ^ in the spreadsheet itself, perhaps by using a macro.
When you call Close_Lotus_Write_File, an end of file record is written
and the file is automatically closed.
Lotus 1-2-3 is a trademark of Lotus Corporation.
*)
Unit Unit123;
Interface
uses crt, dos;
Const
Lotus_Version : integer = 1028; {1028 for Lotus 1}
{1029 for Symphony 1.0}
{1030 for Lotus 2 & Symphony 1.1}
Type
Lotus_Cell_Type = (Version_Type, End_Of_File_Type, Blank_Type,
Integer_Type, Real_Type, Label_Type,
Formula_Type, Unidentified_Type, Range_Type,
Column_Width_Type);
Lotus_Record_Type =
Record
Cell_Type_Code : Integer;
Cell_Length : Integer;
Format : Byte;
Column : integer;
Row : integer;
Cell_Type : Lotus_Cell_Type;
Alpha_Column : String[8];
case integer of
8: (Column_Width : Byte);
11: (Range_Name : string[16];
Range_Start_Column : integer;
Range_Start_Row : integer;
Range_End_Column : integer;
Range_End_Row : integer;
Alpha_Range_Start : string;
Alpha_Range_End : String);
13: (Integer_Value : integer);
14: (Real_Value : double);
15: (Label_Value : string;
Zero : byte);
16: (Formula_Value : double;
Formula_Length : integer;
Formula : array [0 .. 255] of byte);
255: (Unidentified : array [0 .. 511] of byte);
end;
var
Lotus_Read_File_Name : PathStr;
Lotus_Read_File_DirStr : DirStr;
Lotus_Read_File_NameStr : NameStr;
Lotus_Read_File_ExtStr : ExtStr;
Lotus_Read_File : file;
Lotus_Write_File_Name : PathStr;
Lotus_Write_File : file;
Lotus_End_Of_File : boolean;
Lotus_Version_Name : string;
Lotus : Lotus_Record_Type;
Procedure Get_Input_File_Name;
Procedure Open_Lotus_Read_File;
Procedure Get_Version_Name;
Procedure Get_Alpha_Column(var Alpha_Column_ID : String; Column_Number, Row_Number:integer);
Procedure Read_Type_and_Length;
Procedure Read_Format_Info;
Procedure Read_Lotus_Record;
Procedure Print_Lotus_Record;
Procedure Close_Lotus_Read_File;
Procedure Make_New_File_Name;
Procedure Open_Lotus_Write_File;
Procedure Write_Type_and_Length;
Procedure Write_Format_Info;
Procedure Write_Lotus_Record;
Procedure Close_Lotus_Write_File;
implementation
(****************************************************************)
Procedure Get_Input_File_Name; {Solicits the input file name from }
{the console. If the extension is }
{ommitted, it substitutes .WKS }
begin
Write('Enter name of Lotus File '); {Get the file path name}
Readln(Lotus_Read_File_Name);
FSplit(Lotus_Read_File_Name,Lotus_Read_File_DirStr,
Lotus_Read_File_NameStr,Lotus_Read_File_ExtStr);
If Lotus_Read_File_ExtStr = '' then Lotus_Read_File_ExtStr := '.WKS';
Lotus_Read_File_Name := Lotus_Read_File_DirStr + Lotus_Read_File_NameStr +
Lotus_Read_File_ExtStr;
end;
(****************************************************************)
Procedure Read_Type_and_Length; {Could be changed to a single }
{BlockRead since Cell_Type_Code}
{Cell_Length are adjacent in the record}
{definition and on the file.}
begin
BlockRead(Lotus_Read_File, Lotus.Cell_Type_Code, 2);
BlockRead(Lotus_Read_File, Lotus.Cell_Length, 2);
{ OPTIONAL}
{ BlockRead(Lotus_Read_File, Lotus.Cell_Type_Code, 4); }
end;
(****************************************************************)
Procedure Write_Type_and_Length; {Could be changed to a single }
{BlockWrite since format, column}
{and row are adjacent in the record}
{definition and on the file.}
begin
BlockWrite(Lotus_Write_File, Lotus.Cell_Type_Code, 2);
BlockWrite(Lotus_Write_File, Lotus.Cell_Length, 2);
{ OPTIONAL}
{ BlockWrite(Lotus_Write_File, Lotus.Cell_Type_Code, 4); }
end;
(****************************************************************)
Procedure Get_Alpha_Column(var Alpha_Column_ID : String; Column_Number, Row_Number:integer);
Var
First_Char : char;
Second_Char : char;
Row_String : string[6];
Begin
First_Char := char(64 + Column_Number div 26);
Second_Char := char(65 + Column_Number -((byte(First_Char)-64) * 26));
If First_Char = #64 then First_Char := #32;
Str(Row_Number + 1, Row_String);
Alpha_Column_ID := First_Char + Second_Char + Row_String;
end;
(****************************************************************)
Procedure Read_Format_Info; {Could be changed to a single }
{BlockRead since format, column}
{and row are adjacent in the record}
{definition and on the file.}
Var
Alpha_Column_ID : string;
begin
BlockRead(Lotus_Read_File, Lotus.Format, 1);
BlockRead(Lotus_Read_File, Lotus.Column, 2);
BlockRead(Lotus_Read_File, Lotus.Row, 2);
{ OPTIONAL}
{ BlockRead(Lotus_Read_File, Lotus.Format, 5); }
Get_Alpha_Column(Alpha_Column_ID, Lotus.Column, Lotus.Row);
Lotus.Alpha_Column := Alpha_Column_ID;
end;
(****************************************************************)
Procedure Write_Format_Info; {Could be changed to a single }
{BlockWrite since format, column}
{and row are adjacent in the record}
{definition and on the file.}
begin
BlockWrite(Lotus_Write_File, Lotus.Format, 1);
BlockWrite(Lotus_Write_File, Lotus.Column, 2);
BlockWrite(Lotus_Write_File, Lotus.Row, 2);
{ OPTIONAL}
{ BlockWrite(Lotus_Write_File, Lotus.Format, 5); }
end;
(****************************************************************)
Procedure Open_Lotus_Read_File;
begin
{$I-}
Assign(Lotus_Read_File,Lotus_Read_File_Name);
Reset(Lotus_Read_File,1);
If IoResult <> 0 then
begin
Writeln('Error opening file ', Lotus_Read_File_Name);
halt;
end;
{$I+}
Read_Lotus_Record; {Read the first record}
{If the first record is}
{not a Version_Type record}
{then this is not a Lotus File}
If Lotus.Cell_Type <> Version_Type then
begin
Writeln('This is not a Lotus File');
Halt
end;
Lotus_End_Of_File := false;
end;
(****************************************************************)
Procedure Open_Lotus_Write_File;
begin
{$I-}
Assign(Lotus_Write_File,Lotus_Write_File_Name);
ReWrite(Lotus_Write_File,1);
If IoResult <> 0 then
begin
Writeln('Error opening file ', Lotus_Write_File_Name);
halt;
end;
{$I+}
{ Automatically write a version type record at the beginning of the file }
{ Lotus_Version is a typed constant set to Version 1.0 or 1A by default }
{ If you have used Open_Lotus_File to read another Lotus file, then it }
{ will have already read the version type record from the input file }
Lotus.Cell_Type_Code := 0;
Lotus.Cell_Length := 2;
Write_Type_and_Length;
BlockWrite(Lotus_Write_File,Lotus_Version,2);
end;
(****************************************************************)
Procedure Close_Lotus_Read_File;
begin
Close(Lotus_Read_File);
end;
(****************************************************************)
Procedure Close_Lotus_Write_File;
begin
{ Write an end of file record at the end of the file }
Lotus.Cell_Type_Code := 1;
Lotus.Cell_Length := 0;
Write_Type_and_Length; {End the file with a type 1 record}
Close(Lotus_Write_File);
end;
(****************************************************************)
Procedure Get_Version_Name;
begin
Case Lotus_Version of
1028:
Lotus_Version_Name := 'Lotus 1-2-3 Version 1.0 or 1A';
1029:
Lotus_Version_Name := 'Symphony Version 1.0';
1030:
Lotus_Version_Name := 'Lotus 1-2-3 Version 2.0, 2.1 or Symphony Version 1.1';
Else
Lotus_Version_Name := 'Unidentified';
end;
end;
(****************************************************************)
Procedure Read_Lotus_Record;
var
Alpha_Column_ID : string;
begin
FillChar(Lotus, SizeOf(Lotus), #0);
Read_Type_and_Length;
Case Lotus.Cell_Type_Code of
0: begin {Version Record}
{There should be only one}
{record of this type and it}
{will normally be read when}
{you call Open_Lotus_Read_File}
Lotus.Cell_Type := Version_Type;
BlockRead(Lotus_Read_File, Lotus_Version, 2);
Get_Version_Name;
end;
1: begin {End of File}
Lotus.Cell_Type := End_Of_File_Type;
Lotus_End_of_File := True;
end;
8: {Column width type}
begin
Lotus.Cell_Type := Column_Width_Type;
BlockRead(Lotus_Read_File, Lotus.Column, 2);
BlockRead(Lotus_Read_File, Lotus.Column_Width,1);
Get_Alpha_Column(Alpha_Column_ID, Lotus.Column, 0);
Lotus.Alpha_Column := Alpha_Column_ID;
end;
11: begin {Range definition}
Lotus.Cell_Type := Range_Type;
BlockRead(Lotus_Read_File, Lotus.Range_Name[1],16);
Lotus.Range_Name[0] := Char(16);
BlockRead(Lotus_Read_File, Lotus.Range_Start_Column,2);
BlockRead(Lotus_Read_File, Lotus.Range_Start_Row,2);
BlockRead(Lotus_Read_File, Lotus.Range_End_Column,2);
BlockRead(Lotus_Read_File, Lotus.Range_End_Row,2);
Get_Alpha_Column(Alpha_Column_ID, Lotus.Range_Start_Column,
Lotus.Range_Start_Row);
Lotus.Alpha_Range_Start := Alpha_Column_ID;
Get_Alpha_Column(Alpha_Column_ID, Lotus.Range_End_Column,
Lotus.Range_End_Row);
Lotus.Alpha_Range_End := Alpha_Column_ID;
end;
12: begin {Blank Record}
Lotus.Cell_Type := Blank_Type;
Read_Format_Info;
end;
13: begin {Integer}
Lotus.Cell_Type := Integer_Type;
Read_Format_Info;
BlockRead(Lotus_Read_File, Lotus.Integer_Value, 2);
end;
14: begin {Real Value}
Lotus.Cell_Type := Real_Type;
Read_Format_Info;
BlockRead(Lotus_Read_File, Lotus.Real_Value, 8);
end;
15: begin {Label}
Lotus.Cell_Type := Label_Type;
Read_Format_Info;
If Lotus.Cell_Length > 261 then
begin
Writeln('Big problem! Label at Row', Lotus.Row, ' Column ', Lotus.Column, ' has length > 255');
Halt;
end;
BlockRead(Lotus_Read_File, Lotus.Formula, Lotus.Formula_Length);
BlockRead(Lotus_Read_File, Lotus.Label_Value[1], Lotus.Cell_Length - 6);
Lotus.Label_Value[0] := char(Lotus.Cell_Length - 6);
BlockRead(Lotus_Read_File, Lotus.Zero, 1);
end;
16: begin {Formula}
Lotus.Cell_Type := Formula_Type;
Read_Format_Info;
BlockRead(Lotus_Read_File, Lotus.Formula_Value, 8);
BlockRead(Lotus_Read_File, Lotus.Formula_Length, 2);
If Lotus.Formula_Length > 255 then
begin
Writeln('Big problem! Formula cell at Row', Lotus.Row, ' Column ', Lotus.Column, ' has length > 255');
Halt;
end;
BlockRead(Lotus_Read_File, Lotus.Formula, Lotus.Formula_Length);
end;
Else {Unidentified}
begin
Lotus.Cell_Type := Unidentified_Type;
{ Use the following line only if you are sure that the length }
{ of the unidentified data type is less than 512 characters. }
{ I the unidentified data cell is more than 512 byte long, }
{ it could cream the program by overwriting code. }
{ The check on cell length protects against this. But, }
{ if you don't know the maximum cell length, the safest }
{ approach is the approach I have taken, just skip the }
{ unknown data cell }
{ }
{ If Lotus.Cell_Length > 512 then }
{ begin }
{ Writeln('Big problem! Cell at row ', Lotus.Row, ' Column ', Lotus.Coulmn, ' has length > 512'); }
{ Halt; }
{ end; }
{ }
{ BlockRead (Lotus_Read_File, Lotus.Unidentified , Lotus.Cell_Length);}
{ This is the safest way.}
Seek(Lotus_Read_File, FilePos(Lotus_Read_File) + Lotus.Cell_Length);
end;
end;
end;
(****************************************************************)
Procedure Print_Lotus_Record;
begin
If Lotus.Cell_Type = Blank_Type then exit; {If you really want to}
{show all of the blank}
{records, delete this line}
Writeln;
Case Lotus.Cell_Type of
Version_Type:
Writeln(Lotus_Version_Name, ' Id Code = ',Lotus_Version);
End_Of_File_Type:
Writeln('End of File');
Column_Width_Type:
Writeln('Column ', Lotus.Alpha_Column , ' Width = ' , Lotus.Column_Width);
Range_Type:
begin
Writeln('Lotus Range Cell');
Write(' Range Name = ', Lotus.Range_Name);
Write(' Range Start = ', Lotus.Alpha_Range_Start);
Writeln(' Range End = ', Lotus.Alpha_Range_End);
end;
Blank_Type:
begin
Write('Lotus Cell ', Lotus.Alpha_Column);
Write(' Format = ', Lotus.Format);
Writeln(' Blank Cell');
end;
Integer_Type:
begin
Write('Lotus Cell ', Lotus.Alpha_Column);
Write(' Format = ', Lotus.Format);
Writeln(' Integer = ', Lotus.Integer_Value);
end;
Real_Type:
begin
Write('Lotus Cell ', Lotus.Alpha_Column);
Write(' Format = ', Lotus.Format);
Writeln(' Real = ', Lotus.Real_Value);
end;
Label_Type:
begin
Write('Lotus Cell ', Lotus.Alpha_Column);
Write(' Format = ', Lotus.Format);
Writeln(' Label = ', Lotus.Label_Value);
end;
Formula_Type:
begin
Write('Lotus Cell ', Lotus.Alpha_Column);
Write(' Format = ', Lotus.Format);
Writeln(' Formula Value = ', Lotus.Formula_Value);
end;
End_Of_File_Type:
Writeln('End of file detected.');
Unidentified_Type:
begin
Writeln('Unidentified Cell. OpCode = ', Lotus.Cell_Type_Code,
' Length = ', Lotus.Cell_Length);
end;
Else
begin
Writeln(' Unidentified Cell. OpCode = ', Lotus.Cell_Type_Code);
end;
end;
end;
(****************************************************************)
Procedure Write_Lotus_Record;
begin
Case Lotus.Cell_Type of
Column_Width_Type:
begin
Lotus.Cell_Type_Code := 8;
Lotus.Cell_Length := 3;
Write_Type_and_Length;
BlockWrite(Lotus_Write_File , Lotus.Column, 2);
BlockWrite(Lotus_Write_File , Lotus.Column_Width, 1);
end;
Range_Type:
begin
Lotus.Cell_Type_Code := 11;
Lotus.Cell_Length := 24;
Write_Type_and_Length;
BlockWrite(Lotus_Write_File,Lotus.Range_Name[1], 16);
BlockWrite(Lotus_Write_File,Lotus.Range_Start_Column,2);
BlockWrite(Lotus_Write_File,Lotus.Range_Start_Row,2);
BlockWrite(Lotus_Write_File,Lotus.Range_End_Column,2);
BlockWrite(Lotus_Write_File,Lotus.Range_End_Row,2);
end;
Blank_Type:
Exit;
Integer_Type:
begin
Lotus.Cell_Type_Code := 13;
Lotus.Cell_Length := 7;
Write_Type_and_Length;
Write_Format_Info;
BlockWrite(Lotus_Write_File,Lotus.Integer_Value,2);
end;
Real_Type:
begin
Lotus.Cell_Type_Code := 14;
Lotus.Cell_Length := 13;
Write_Type_and_Length;
Write_Format_Info;
BlockWrite(Lotus_Write_File,Lotus.Real_Value,8);
end;
Label_Type:
begin
Lotus.Cell_Type_Code := 15;
Lotus.Cell_Length := 6 + Length(Lotus.Label_Value);
Lotus.Zero := 0;
Write_Type_and_Length;
Write_Format_Info;
BlockWrite(Lotus_Write_File,Lotus.Label_Value[1],Length(Lotus.Label_Value));
BlockWrite(Lotus_Write_File,Lotus.Zero, 1);
end;
Formula_Type: {NOTE: ONLY COPIES OUT THE CURRENT VALUE AS A REAL}
{ If you want to copy the formula then also }
{ BlockWrite Lotus.Formula_Value. }
{ See Read_Lotus_File for how to interpret length }
{ Also, you must change the Cell_Type_Code to 16 }
begin
Lotus.Cell_Type_Code := 14;
Lotus.Cell_Length := 13;
Write_Type_and_Length;
Write_Format_Info;
BlockWrite(Lotus_Write_File,Lotus.Formula_Value,8);
end;
Else
begin
end;
end;
end;
(****************************************************************)
Procedure Make_New_File_Name;
begin
Lotus_Write_File_Name := Lotus_Read_File_DirStr + Lotus_Read_File_NameStr +
'.WK!';
end;
begin
end.